package com.sinaapp.zygl.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.sinaapp.zygl.bean.Customer;
import com.sinaapp.zygl.dao.CustomerDao;
import com.sinaapp.zygl.util.DateUtil;
import com.sinaapp.zygl.util.DbUtil;

public class CustomerDaoImpl implements CustomerDao {

	/*cid;cname; cgender;caddress; cbirth; ctel; ccompany; cposition;*/
	
	/*cbirthStart;cbirthEnd;*/

	@Override
	public boolean add(Customer ctm) {
		Connection conn = DbUtil.getConnection();
		String sql = "insert into customer(cid,cname,cgender,caddress,cbirth,ctel,ccompany,cposition) values(?,?,?,?,?,?,?,?)";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, String.valueOf(queryLastCID() + 1));
			pstmt.setString(2, ctm.getCname());
			pstmt.setString(3, ctm.getCgender());
			pstmt.setString(4, ctm.getCaddress());
			pstmt.setString(5, DateUtil.dateToString(ctm.getCbirth(), "yyyy-MM-dd"));
			pstmt.setString(6, ctm.getCtel());
			pstmt.setString(7, ctm.getCcompany());
			pstmt.setString(8, ctm.getCposition());
			pstmt.executeUpdate();
			return true;
			// System.out.println("用户已添加");
		} catch (SQLException e) {
			System.out.println("用户添加失败!");
		} finally {
			DbUtil.closeConnection(conn);
		}
		return false;
	}

	@Override
	public boolean delete(String cid) {
		Connection conn = DbUtil.getConnection();
		try {
			String sql = "delete from customer where cid=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, cid);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException ex) {
			Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			DbUtil.closeConnection(conn);
		}
		return false;
	}

	@Override
	public boolean update(Customer ctm) {
		Connection conn = DbUtil.getConnection();
		String sql = "update customer set cname=?,cgender=?,caddress=?,cbirth=?,ctel=?,ccompany=?,cposition=? where cid=? ";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, ctm.getCname());
			pstmt.setString(2, ctm.getCgender());
			pstmt.setString(3, ctm.getCaddress());
			pstmt.setString(4, DateUtil.dateToString(ctm.getCbirth(), "yyyy-MM-dd"));
			pstmt.setString(5, ctm.getCtel());
			pstmt.setString(6, ctm.getCcompany());
			pstmt.setString(7, ctm.getCposition());
			pstmt.setString(8, ctm.getCid());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException ex) {
			Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			DbUtil.closeConnection(conn);
		}
		return false;
	}

	@Override
	public int queryCustomerTotal(Customer ctm) {
		Connection conn = DbUtil.getConnection();
		String sql = addSqlString("select count(cid) FROM customer", ctm);
		int total = 0;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
		} finally {
			DbUtil.closeConnection(conn);
		}
		return total;
	}

	@Override
	public List<Customer> getCustomers(int page, int rows, String sort, String order, Customer ctm) {
		Connection conn = DbUtil.getConnection();
		List<Customer> queryCtmlist = new ArrayList<Customer>();
		try {
			String sql1 = "";
			String sql2 = "";
			if ((sort == null || sort.equals("")) | (order == null || order.equals(""))) {
				sql2 = addSqlString("select * FROM customer", ctm) + " ORDER BY id asc LIMIT 0,0";
				// System.out.println(sql2 + " "+ DateUtil.dateToString(new
				// Date(), "yyyy-MM-dd HH:mm:ss"));
			} else {
				sql1 = " " + ((page - 1) * rows) + "," + rows + "";
				// select * FROM Customer ORDER BY id asc LIMIT 5,10
				sql2 = addSqlString("select * from customer", ctm) + " order by " + sort + " "
						+ order + " " + "limit" + sql1;
				// System.out.println(sql2 + " "+ DateUtil.dateToString(new
				// Date(), "yyyy-MM-dd HH:mm:ss"));
			}
			PreparedStatement pstmt = conn.prepareStatement(sql2);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Customer c = new Customer();
				c.setCid(rs.getString(1));
				c.setCname(rs.getString(2));
				c.setCgender(rs.getString(3));
				c.setCaddress(rs.getString(4));
				c.setCbirth(rs.getTimestamp(5));
				c.setCtel(rs.getString(6));
				c.setCcompany(rs.getString(7));
				c.setCposition(rs.getString(8));
				queryCtmlist.add(c);
			}
			return queryCtmlist;
		} catch (SQLException ex) {
			Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			DbUtil.closeConnection(conn);
		}
		return null;
	}

	/**
	 * 查询最大的cid
	 * @return
	 */
	private int queryLastCID() {
		Connection conn = DbUtil.getConnection();
		String sql = "select * from customer order by cid desc";
		int cid = 0;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				cid = Integer.parseInt(rs.getString(1));
			}
		} catch (SQLException e) {
		} finally {
			DbUtil.closeConnection(conn);
		}
		return cid;
	}

	/**
	 * 拼接sql语句
	 * @param input
	 * @param ctm
	 * @return
	 */
	private String addSqlString(String input, Customer ctm) {
		String sql = input;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String date = sdf.format(new java.util.Date());
		// cid,cname,cgender,caddress,cbirth,ctel,ccompany,cposition
		if (ctm.getCid() != null && ctm.getCid().length() != 0) {
			// select * FROM user WHERE name like '土%%'or name like '%土%' ORDER
			// BY id asc LIMIT 0,10
			sql += " where cid like '" + ctm.getCid() + "%%' or cid like " + "'%" + ctm.getCid()
					+ "%' ";
			return sql;
		}
		if (ctm.getCname() != null && ctm.getCname().length() != 0) {
			sql += " where cname like '" + ctm.getCname() + "%%' or cname like " + "'%"
					+ ctm.getCname() + "%' ";
			return sql;
		}
		if (ctm.getCgender() != null && ctm.getCgender().length() != 0) {
			sql += " where cgender like '" + ctm.getCgender() + "%%' or cgender like " + "'%"
					+ ctm.getCgender() + "%' ";
			return sql;
		}
		if (ctm.getCaddress() != null && ctm.getCaddress().length() != 0) {
			sql += " where caddress like '" + ctm.getCaddress() + "%%' or caddress like " + "'%"
					+ ctm.getCaddress() + "%' ";
			return sql;
		}
		// 时间
		if ((ctm.getCbirthStart() != null) && (ctm.getCbirthEnd() == null)) {
			// select * FROM user WHERE cbirth BETWEEN '2015-01-03 00:00:00' AND
			// '2015-01-04 23:59:59' ORDER BY id asc LIMIT 0,10
			String s = DateUtil.dateToString(ctm.getCbirthStart(), "yyyy-MM-dd");
			sql += " where cbirth  BETWEEN '" + s + "' and '" + date + "'";
			return sql;
		} else if ((ctm.getCbirthStart() != null) && (ctm.getCbirthEnd() != null)) {
			String s1 = DateUtil.dateToString(ctm.getCbirthStart(), "yyyy-MM-dd");
			String s2 = DateUtil.dateToString(ctm.getCbirthEnd(), "yyyy-MM-dd");
			sql += " where cbirth  BETWEEN '" + s1 + "' and '" + s2 + "'";
			return sql;
		} else if ((ctm.getCbirthStart() == null) && (ctm.getCbirthEnd() != null)) {
			String s2 = DateUtil.dateToString(ctm.getCbirthEnd(), "yyyy-MM-dd");
			sql += " where cbirth  BETWEEN '1970-01-01 00:00:00' and '" + s2 + "'";
			return sql;
		}
		if (ctm.getCtel() != null && ctm.getCtel().length() != 0) {
			sql += " where ctel like '" + ctm.getCtel() + "%%' or ctel like " + "'%"
					+ ctm.getCtel() + "%' ";
			return sql;
		}
		if (ctm.getCcompany() != null && ctm.getCcompany().length() != 0) {
			sql += " where ccompany like '" + ctm.getCcompany() + "%%' or ccompany like " + "'%"
					+ ctm.getCcompany() + "%' ";
			return sql;
		}
		if (ctm.getCposition() != null && ctm.getCposition().length() != 0) {
			sql += " where cposition like '" + ctm.getCposition() + "%%' or cposition like " + "'%"
					+ ctm.getCposition() + "%' ";
			return sql;
		}
		return sql;
	}
}
